﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace WebApplication1
{
    public partial class Recherche : System.Web.UI.Page
    {
        //protected const string connectionString = @"Provider=SQLOLEDB;server=FORMA1614;Database=magicrecettes;User ID=magicrecettes;Password=cook;Trusted_Connection=False";
        //protected const string query = "SELECT * FROM Recette where tps_preparation between @minduree and @maxduree";

       
        protected void Page_Load(object sender, EventArgs e)
        {
            
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            if (Temps.Checked)
            {
                if ((minduree.Text != string.Empty) & (maxduree.Text != string.Empty))
                {
                    SqlConnection MyConnection;
                    SqlCommand MyCommand;
                    MyCommand = new SqlCommand();

                    MyConnection = new SqlConnection();
                    MyConnection.ConnectionString = "server=FORMA1614;Database=magicrecettes;User ID=magicrecettes;Password=cook";

                    SqlDataReader MyReader;

                    SqlParameter mindureeParam;
                    SqlParameter maxdureeParam;
                    MyCommand.CommandText = "SELECT * FROM Recette where tps_preparation+tps_cuisson between @minduree and @maxduree";
                    MyCommand.CommandType = CommandType.Text;
                    MyCommand.Connection = MyConnection;

                    mindureeParam = new SqlParameter();
                    mindureeParam.ParameterName = "@minduree";
                    mindureeParam.SqlDbType = SqlDbType.Int;
                    //mindureeParam.Size = 25;
                    mindureeParam.Direction = ParameterDirection.Input;
                    mindureeParam.Value = minduree.Text;
                    MyCommand.Parameters.Add(mindureeParam);

                    maxdureeParam = new SqlParameter();
                    maxdureeParam.ParameterName = "@maxduree";
                    maxdureeParam.SqlDbType = SqlDbType.Int;
                    //maxdureeParam.Size = 25;
                    maxdureeParam.Direction = ParameterDirection.Input;
                    maxdureeParam.Value = maxduree.Text;
                    MyCommand.Parameters.Add(maxdureeParam);

                    MyCommand.Connection.Open();
                    MyReader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection);

                    Recette.DataSource = MyReader;
                    Recette.DataBind();

                    MyCommand.Dispose();
                    MyConnection.Dispose();
                }
                else
                {
                    Response.Write("durée non complétée");
                }
            }

            if (Ingredients.Checked)
            {
                Response.Write(DropDownListIng.SelectedItem.Text);
                Response.Write(DropDownListIng.SelectedItem.Value);
                SqlConnection MyConnection;
                SqlCommand MyCommand;
                MyCommand = new SqlCommand();

                MyConnection = new SqlConnection();
                MyConnection.ConnectionString = "server=FORMA1614;Database=magicrecettes;User ID=magicrecettes;Password=cook";

                SqlDataReader MyReader;

                SqlParameter ingParam;
                MyCommand.CommandText = "SELECT id_recette,nom,sous_titre,tps_preparation,tps_cuisson,Composition.ingredient_id FROM Recette" +
                " left join Composition on id_recette=recette_id where ingredient_id=@iding";
                MyCommand.CommandType = CommandType.Text;
                MyCommand.Connection = MyConnection;

                ingParam = new SqlParameter();
                ingParam.ParameterName = "@iding";
                ingParam.SqlDbType = SqlDbType.Int;
                //mindureeParam.Size = 25;
                ingParam.Direction = ParameterDirection.Input;
                ingParam.Value = DropDownListIng.SelectedItem.Value;
                MyCommand.Parameters.Add(ingParam);

                MyCommand.Connection.Open();
                 MyReader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection);

                Recette.DataSource = MyReader;
                Recette.DataBind();

                MyCommand.Dispose();
                MyConnection.Dispose();
            }
            if (Exclure.Checked)
            {
                Response.Write(DropDownListIng.SelectedItem.Text);
                Response.Write(DropDownListIng.SelectedItem.Value);
                SqlConnection MyConnection;
                SqlCommand MyCommand;
                MyCommand = new SqlCommand();

                MyConnection = new SqlConnection();
                MyConnection.ConnectionString = "server=FORMA1614;Database=magicrecettes;User ID=magicrecettes;Password=cook";

                SqlDataReader MyReader;

                SqlParameter ingParam;
                MyCommand.CommandText = "SELECT id_recette,nom,sous_titre,tps_preparation,tps_cuisson FROM Recette" +
                " left join Composition on id_recette=recette_id where Composition.ingredient_id<>@iding" +
                " except SELECT id_recette,nom,sous_titre,tps_preparation,tps_cuisson FROM Recette" +
                " left join Composition on id_recette=recette_id where Composition.ingredient_id=@iding";
                MyCommand.CommandType = CommandType.Text;
                MyCommand.Connection = MyConnection;

                ingParam = new SqlParameter();
                ingParam.ParameterName = "@iding";
                ingParam.SqlDbType = SqlDbType.Int;
                //mindureeParam.Size = 25;
                ingParam.Direction = ParameterDirection.Input;
                ingParam.Value = DropDownListIng.SelectedItem.Value;
                MyCommand.Parameters.Add(ingParam);

                MyCommand.Connection.Open();
                MyReader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection);

                Recette.DataSource = MyReader;
                Recette.DataBind();

                MyCommand.Dispose();
                MyConnection.Dispose();
            }
        }

        protected void Temps_CheckedChanged(object sender, EventArgs e)
        {
            Response.Write("temps");
            //panel.Visible = ((CheckBox)sender).Checked;
        }

        protected void Ingredients_CheckedChanged(object sender, EventArgs e)
        {
            
           Response.Write("ingrédients");
            
            //((CheckBox)sender).Text = panel.Visible ? "panel invisible" : "panel visible";
        }
        
    }
}